/*
 * Copyright (C) 2009 Dimitrios C. Michalakos
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package com.restsql.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * MySQLColumnInfo can be used to get information about the types and properties of the columns in a table or a resultset.
 * @author dimitris@jmike.gr
 */
public class MySQLColumnInfo extends GenericColumnInfo implements ColumnInfo {

    /**
     * Gets information for the designated table's columns and constructs a new ColumnInfo.
     * @param db the parent database.
     * @param table the name of the table.
     * @throws SQLException
     */
    public MySQLColumnInfo(Database db, String table) throws SQLException {
        if (db.getTableInfo().exists(table)) {//table exists in database
            /* set resultset */
            final String sql = "SELECT `COLUMN_NAME`, `DATA_TYPE`, `COLUMN_TYPE`, `COLUMN_DEFAULT`, `IS_NULLABLE`, `CHARACTER_MAXIMUM_LENGTH`, `NUMERIC_PRECISION`, `NUMERIC_SCALE`, `COLUMN_KEY`, `EXTRA` FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? ORDER BY `ORDINAL_POSITION` ASC;";
            PreparedStatement ps = db.getPreparedStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);//Create prepared statement
            ps.setString(1, db.getSchemaName());//set schema
            ps.setString(2, table);//set table
            ResultSet rs = ps.executeQuery();
            /* count the columns in resultset */
            rs.beforeFirst();//reset rs cursor
            int i = 0;
            while (rs.next()) {//iterate rs
                i++;
            }
            /* set the types and properties */
            this.table = new String[i];
            column = new String[i];
            datatype = new DatatypeEnum[i];
            signed = new boolean[i];
            primaryKey = new boolean[i];
            nullable = new boolean[i];
            autoInc = new boolean[i];
            size = new long[i];
            integerDigits = new int[i];
            fractionalDigits = new int[i];
            defaultValue = new String[i];
            possibleValues = new String[i];
            rs.beforeFirst();//reset rs cursor
            i = 0;
            while (rs.next()) {//iterate rs again
                this.table[i] = table;//set table
                column[i] = rs.getString("COLUMN_NAME");//set column
                index.put(column[i], Integer.valueOf(i));//put (column name, internal id)
                datatype[i] = DatatypeEnum.valueOf(rs.getString("DATA_TYPE").toUpperCase());//set datatype
                signed[i] = !rs.getString("COLUMN_TYPE").endsWith("unsigned");//set signed boolean flag
                primaryKey[i] = rs.getString("COLUMN_KEY").equals("PRI");//set primary key boolean flag

                if(rs.getString("COLUMN_KEY").equals("PRI")) {
                    primaryKey_col_positions.add(i+1);
                }

                nullable[i] =  rs.getString("IS_NULLABLE").equals("YES");
                autoInc[i] =  rs.getString("EXTRA").contains("auto_increment");
                size[i] = rs.getLong("NUMERIC_PRECISION");//set size as numeric precision - plz note that precision for mysql numeric fields means total digits, i.e. in case of 25,458 the precision is 5 (not 2).
                if (rs.wasNull()) {//numeric precision was null - datatype is not numeric
                    size[i] = rs.getLong("CHARACTER_MAXIMUM_LENGTH");
                }
                fractionalDigits[i] = rs.getInt("NUMERIC_SCALE");
                integerDigits[i] = rs.getInt("NUMERIC_PRECISION") - fractionalDigits[i];
                defaultValue[i] = rs.getString("COLUMN_DEFAULT");
                if (datatype[i] == DatatypeEnum.ENUM || datatype[i] == DatatypeEnum.SET) {
                    possibleValues[i] = rs.getString("COLUMN_TYPE").replaceAll("ENUM|enum|SET|set|\\(|\\)|'", "");
                } else {
                    possibleValues[i] = null;//any value is possible
                }
                i++;
            }
            /* close resultset */
            rs.close();
            ps.close();
        } else {//table not found in database
            final SQLExceptionState state = SQLExceptionState.TABLE_NOT_FOUND;
            throw new SQLException("Table " + table + " cannot be found in database.", state.name(), state.code());
        }
    }

    /**
     * Parses the specified ResultSet and constructs a new MySQLColumnInfo.
     * @param db the parent database.
     * @param rs the dataset.
     * @throws SQLException
     */
    public MySQLColumnInfo(Database db, ResultSet rs) throws SQLException {
        super(db, rs);
    }
}
